<?php defined('SYSPATH') or die('No direct access allowed.');


class Model_Desktop_Project extends ORM {

		// Relationships
	protected $_belongs_to = array('item'    => array('model' => 'desktop_item','foreign_key' => 'item_id'),
                                       'field'   => array('model' => 'field','foreign_key' => 'field_id'),
                                       'licence' => array('model' => 'project_licence', 'foreign_key' => 'licence_id'));
        
        protected $_has_many = array('containers'    => array('model' => 'project_container', 'foreign_key' => 'project_id'),
                                     'appreciations' => array('model' => 'project_appreciation', 'foreign_key' => 'project_id'),
                                     'tags'          => array('model' => 'project_tag', 'foreign_key' => 'project_id'),
                                     'comments'      => array('model' => 'project_comment', 'foreign_key' => 'project_id'),
                                     'views'         => array('model' => 'project_view', 'foreign_key' => 'project_id'));
        
        public function get_project($project_id)
        {
            $project_ = DB::select(array('user_profiles.url', 'user_url'),
                                  array('user_profiles.user_id', 'user_id'),
                                  array('user_profiles.name', 'user_name'),
                                  array('user_profiles.surname', 'user_surname'),
                                  array('user_profiles.description', 'user_description'),
                                  array('user_profiles.file_id', 'user_photo'),
                                  array('user_images.id','user_image_id'),
                                  array('user_images.name','user_image_name'),
                                  array('user_images.extension','user_image_extension'),
                                  array('desktop_items.thumbnail_id','thumbnail'),
                                  array('item_thumbnails.id','thumbnail_id'),
                                  array('item_thumbnails.name','thumbnail_name'),
                                  array('item_thumbnails.extension','thumbnail_extension'),
                                  array('desktop_projects.licence_id', 'licence_id'),
                                  array('desktop_projects.id', 'id'),
                                  array('desktop_items.date_created', 'date_created'),
                                  array('desktop_items.date_modify', 'date_modify'),
                                  array('desktop_items.title', 'title'),
                                  array('desktop_items.description', 'description'),
                                  array('desktop_items.id', 'item_id'),
                                  array('fields.name', 'field_name'))
                           ->from('desktop_projects')
                           ->join('fields')->on('desktop_projects.field_id', '=', 'fields.id')
                           ->join('desktop_items')->on('desktop_projects.item_id', '=', 'desktop_items.id')
                           ->join('user_desktops')->on('desktop_items.desktop_id', '=', 'user_desktops.id')
                           ->join('user_profiles')->on('user_desktops.user_id', '=', 'user_profiles.user_id')
                           ->join(array('files','user_images'), 'LEFT')->on('user_profiles.file_id', '=', 'user_images.id')
                           ->join(array('files','item_thumbnails'), 'LEFT')->on('desktop_items.thumbnail_id', '=', 'item_thumbnails.id')
                           ->where('desktop_projects.id', '=', $project_id)
                           ->as_object()
                           ->execute();
            
            $project = $project_[0];
            $project->user_photo = Media::get_file_path($project->user_image_name, 
                                                            $project->user_image_id, 
                                                            $project->user_image_extension, 
                                                            false, 
                                                            'square', 
                                                            false);
            if ($project->thumbnail)
                $project->thumbnail = Media::get_file_path($project->thumbnail_name, 
                                                                $project->thumbnail_id, 
                                                                $project->thumbnail_extension, 
                                                                true, 
                                                                'default', 
                                                                false);
            return $project;
        }
        
        public function get_projects($limit = null, $offset = null, $fields = null, $word = null, $time = null, $filter = null)
        {
  /*start old version  
   *   
            $comments = DB::select('project_id',
                                   array('COUNT("*")', 'total_comments'))
                               ->from('project_comments')
                               ->group_by('project_id');
            
            $views = DB::select('project_id',
                                array('COUNT("*")', 'total_views'))
                               ->from('project_views')
                               ->group_by('project_id');
            
            $appreciations = DB::select('project_id',
                                        array('COUNT("*")', 'total_appreciations'))
                               ->from('project_appreciations')
                               ->group_by('project_id');
            
            $tags = DB::select()
                      ->from('project_tags')
                      ->where('project_tags.project_id', '=', DB::expr('`desktop_projects`.`id`'))
                      ->where('project_tags.name', '=', $word);
            
          $projects_ = DB::select(array('user_profiles.url', 'user_url'),
                                  array('user_profiles.user_id', 'user_id'),
                                  array('user_profiles.name', 'user_name'),
                                  array('user_profiles.surname', 'user_surname'),
                                  array('user_profiles.description', 'user_description'),
                                  array('desktop_items.thumbnail_id','thumbnail'),
                                  array('item_thumbnails.id','thumbnail_id'),
                                  array('item_thumbnails.name','thumbnail_name'),
                                  array('item_thumbnails.extension','thumbnail_extension'),
                                  array('desktop_projects.licence_id', 'licence_id'),
                                  array('desktop_projects.id', 'id'),
                                  array('desktop_items.date_created', 'date_created'),
                                  array('desktop_items.title', 'title'),
                                  array('desktop_items.description', 'description'),
                                  array('desktop_items.icon_id','icon'),
                                  array('item_icons.id','icon_id'),
                                  array('item_icons.name','icon_name'),
                                  array('item_icons.extension','icon_extension'),
                                  array('fields.name', 'field_name'),
                                  array(DB::expr('COALESCE(views.total_views, 0)'),'view_amount'),
                                  array(DB::expr('COALESCE(comments.total_comments, 0)'),'comment_amount'),
                                  array(DB::expr('COALESCE(appreciations.total_appreciations,0)'),'appreciation_amount'))
                           ->from('desktop_projects')
                           ->join('fields')->on('desktop_projects.field_id', '=', 'fields.id')
                           ->join('desktop_items')->on('desktop_projects.item_id', '=', 'desktop_items.id')
                           ->join('user_desktops')->on('desktop_items.desktop_id', '=', 'user_desktops.id')
                           ->join('user_profiles')->on('user_desktops.user_id', '=', 'user_profiles.user_id')
                           ->join(array($views,'views'), 'LEFT')->on('views.project_id', '=', 'desktop_projects.id')
                           ->join(array($comments,'comments'), 'LEFT')->on('comments.project_id', '=', 'desktop_projects.id')
                           ->join(array($appreciations,'appreciations'), 'LEFT')->on('appreciations.project_id', '=', 'desktop_projects.id')
                           ->join(array('files','item_icons'), 'LEFT')->on('desktop_items.icon_id', '=', 'item_icons.id')
                           ->join(array('files','item_thumbnails'), 'LEFT')->on('desktop_items.thumbnail_id', '=', 'item_thumbnails.id')
                           ->where('desktop_items.date_modify', 'IS NOT', NULL);
            if($limit)
            {
                $projects_->limit((int) $limit);
            }
            
            if($offset)
            {
                $projects_->offset((int) $offset);
            }
                 
            if(is_array($fields))
            {
                $projects_->where('desktop_projects.field_id', 'IN', $fields);
            } 
            elseif($fields != null)
            {
                $projects_->where('desktop_projects.field_id', '=',(int) $fields);
            }
            
            if($word)
            {
                  //$projects_->join('project_tags')->on('project_tags.project_id', '=', 'desktop_projects.id')
                  
                  $projects_->where_open()
                            ->where(null,'exists',$tags)
                            ->or_where('user_profiles.name', '=', $word)
                            ->or_where('user_profiles.surname', '=', $word)
                            ->or_where(DB::expr("CONCAT(`user_profiles`.`name`, ' ', `user_profiles`.`surname`)"), '=', $word)
                            ->where_close();
                        
            }
            
            if($filter)
            {
                switch($filter)
                {
                    case 0: //most recent
                        $projects_->order_by('desktop_items.date_created', 'ASC');
                        break;
                    case 1: //most viewed
                        $projects_->order_by('view_amount', 'DESC');
                        break;
                    case 2: //most discussed
                        $projects_->order_by('comment_amount', 'DESC');
                        break;
                    case 3: //most appreciated
                        $projects_->order_by('appreciation_amount', 'DESC');
                        break;
                }
            }
            
            if($time)
            {
                switch($time)
                {                
                    case '1': //today
                        $projects_->where('desktop_items.date_created', '>', date('Y-m-d 00:00:00', time()));
                        break;
                    case '2': //this week
                        $projects_->where('desktop_items.date_created', '>', date('Y-m-d 00:00:00', strtotime ("LAST MONDAY")));
                        break;
                    case '3': //this month
                        $projects_->where('desktop_items.date_created', '>', date('Y-m-01 00:00:00', time()));
                        break;
                    case '4': //this year
                        $projects_->where('desktop_items.date_created', '>', date('Y-01-01 00:00:00', time()));
                        break;
                }
            }
            //echo $projects_ ; die;
            
            $projects_ = $projects_->as_object()
                                   ->execute(); */
//end old version
            
            //sphinx start
            $sphinx = new Sphinx();
            $sphinx->SetServer('127.0.0.1', 3312); 
            $sphinx->SetConnectTimeout(5);
            $sphinx->SetMatchMode(SPH_MATCH_ALL); 
       

            if ($limit)
            {       
                $sphinx->SetLimits((int)$offset, (int)$limit);
            }
            
            if(is_array($fields))
            {
                $sphinx->SetFilter('field_id', $fields);
            } 
            elseif((int) $fields)
            {
                 $sphinx->SetFilter('field_id', array($fields));
            }
                
            if($filter)
            {
                switch($filter)
                {
                    case 0: //most recent
                        $sphinx->SetSortMode(SPH_SORT_ATTR_ASC, 'date_created');
                        break;
                    case 1: //most viewed
                        $sphinx->SetSortMode(SPH_SORT_ATTR_DESC, 'view_amount');
                        break;
                    case 2: //most discussed
                        $sphinx->SetSortMode(SPH_SORT_ATTR_DESC, 'comment_amount');
                        break;
                    case 3: //most appreciated
                        $sphinx->SetSortMode(SPH_SORT_ATTR_DESC, 'appreciation_amount');
                        break;
                }
            }
           
            if($time)
            {
                switch($time)
                {                
                    case '1': //today
                        $sphinx->SetFilterRange('date_created', mktime(0, 0, 0), time());
                        break;
                    case '2': //this week
                        $sphinx->SetFilterRange('date_created', strtotime ("last Monday"), time());
                        break;
                    case '3': //this month
                        $sphinx->SetFilterRange('date_created', mktime(0, 0, 0, date('m'), 1), time());
                        break;
                    case '4': //this year
                        $sphinx->SetFilterRange('date_created', mktime(0, 0, 0, 1, 1), time());
                        break;
                }
            }
                    
            $result = $sphinx->Query((string) $word, 'i_project');
            if($result === false)
            {
                echo $sphinx->GetLastError();
            }
            else
            {
                if($sphinx->GetLastWarning() != '')
                {
                    echo $sphinx->GetLastWarning(); 
                }
            }

            //echo "<pre>"; var_dump($result);echo "</pre>";
            //sphinx end 
      
            $projects = array();
            
            //query based od sphinx result
            if(isset($result['matches']))
            {
            
            $projects_ = DB::select(array('user_profiles.url', 'user_url'),
                                    array('user_profiles.user_id', 'user_id'),
                                    array('user_profiles.name', 'user_name'),
                                    array('user_profiles.surname', 'user_surname'),
                                    array('user_profiles.description', 'user_description'),
                                    array('desktop_items.thumbnail_id','thumbnail'),
                                    array('item_thumbnails.id','thumbnail_id'),
                                    array('item_thumbnails.name','thumbnail_name'),
                                    array('item_thumbnails.extension','thumbnail_extension'),
                                    array('desktop_projects.id', 'id'),
                                    array('desktop_items.title', 'title'),
                                    array('desktop_items.description', 'description'),
                                    array('desktop_items.icon_id','icon'),
                                    array('item_icons.id','icon_id'),
                                    array('item_icons.name','icon_name'),
                                    array('item_icons.extension','icon_extension'),
                                    array('fields.name', 'field_name'))                    
                           ->from('desktop_projects')
                           ->join('fields')->on('desktop_projects.field_id', '=', 'fields.id')
                           ->join('desktop_items')->on('desktop_projects.item_id', '=', 'desktop_items.id')
                           ->join('user_desktops')->on('desktop_items.desktop_id', '=', 'user_desktops.id')
                           ->join('user_profiles')->on('user_desktops.user_id', '=', 'user_profiles.user_id')
                           ->join(array('files','item_icons'), 'LEFT')->on('desktop_items.icon_id', '=', 'item_icons.id')
                           ->join(array('files','item_thumbnails'), 'LEFT')->on('desktop_items.thumbnail_id', '=', 'item_thumbnails.id')
                           ->where('desktop_projects.id', 'IN', array_keys($result['matches']))
                           ->order_by(db::expr('FIND_IN_SET(desktop_projects.id,\''.implode(',', array_keys($result['matches'])).'\')'))
                           ->as_object()
                           ->execute(); 
       
            
            
           
                foreach($projects_ as $project)
                {
                     $project->icon = Media::get_file_path($project->icon_name, 
                                                            $project->icon_id, 
                                                            $project->icon_extension, 
                                                            false, 
                                                            'default', 
                                                            false);

                     if ($project->thumbnail)
                         $project->thumbnail = Media::get_file_path($project->thumbnail_name, 
                                                                    $project->thumbnail_id, 
                                                                    $project->thumbnail_extension, 
                                                                    true, 
                                                                    'default', 
                                                                    false);
                  
                     $project->appreciation_amount = $result['matches'][$project->id]['attrs']['appreciation_amount'];
                     $project->comment_amount = $result['matches'][$project->id]['attrs']['comment_amount'];
                             
                     $projects[] = $project;
                } 
            }
            
            return $projects;
        }
        
//        public function is_owner($project_id, $user_id)
//        {
//            $query = DB::select('COUNT("*")')
//                       ->from('desktop_projects')
//                       ->join('desktop_items')->on('desktop_project.item_id', '=', 'desktop_items.id')
//                       ->join('user_desktop')->on('desktop_items.desktop_id', '=', 'user_desktop.id')
//                       ->where('user_desktop.user_id', '=', $user_id)
//                       ->as_assoc()
//                       ->execute();
//            
//            return (bool) $query[0];
//        }
        
}